package com.jackrain.nea.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jackrain.nea.config.Resources;
import com.jackrain.nea.core.query.ColumnLink;
import com.jackrain.nea.core.schema.Column;
import com.jackrain.nea.core.schema.Table;
import com.jackrain.nea.core.schema.TableManager;
import com.jackrain.nea.exception.NDSException;
import com.jackrain.nea.util.PairTable;
import com.jackrain.nea.util.Tools;
import com.jackrain.nea.util.Validator;
import com.jackrain.nea.web.query.*;
import com.jackrain.nea.web.security.Directory;
import com.jackrain.nea.web.security.SecurityWebUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

@Slf4j
public class AjaxUtils {
    
    
    
    public static QueryRequestImpl parseQuery(JSONObject jo, QuerySession qsession, int userId, Locale locale,Table table) throws Exception {

        QueryRequestImpl query = new com.jackrain.nea.core.db.QueryRequestImpl(qsession);

        TableManager manager = qsession.getTableManager();

//        Table table = manager.getTable(jo.getIntValue("tableid"));


        Boolean includeUI = Tools.getBoolean(jo.getString("column_include_uicontroller"), false);

        query.setMainTable(table.getId());

        /**
         * Support both column_masks and qlcid(QueryListConfig.id), and column_masks has higher priority
         */
    
        JSONArray masks = jo.getJSONArray("column_masks");

        int qlcId = jo.getIntValue("qlcid");// 0 means meta default qlc, so -2 means not set qlc
        QueryListConfig qlc = null;
        //try loading from QueryListConfig
        if (qlcId == 0) {
            qlc = QueryListConfigManager.getInstance().getMetaDefault(table.getId(),0,manager,false,includeUI);
        } else if (qlcId > 0) {
            qlc = QueryListConfigManager.getInstance().getQueryListConfig(qlcId, manager);
        }
    
    
    
        //是否返回搜索查询表单模式
        Boolean isdroplistsearch=Tools.getBoolean(jo.getBoolean("isdroplistsearch"),false);
        
        
        
        if (isdroplistsearch) {
            String searchqlc="search_"+table.getName();//jo.getString("SearchListqlc");
            qlc = QueryListConfigManager.getInstance().getQueryListConfig(searchqlc);
            if (qlc == null) {
                masks = new JSONArray();
                masks.add(Column.MASK_QUERY_LIST);
            }
        }
        
        
        if (masks != null && masks.size() > 0) {
            int[] cmasks = new int[masks.size()];
            for (int i = 0; i < cmasks.length; i++) {
                cmasks[i] = masks.getIntValue(i);
            }
            log.debug("jo:" + masks + ", masks class:" + masks.getClass());
            //Select
            query.addSelection(table.getPrimaryKey().getId());
            query.addColumnsToSelection(cmasks, includeUI);
        } else if (qlc != null) {
            query.addSelection(table.getPrimaryKey().getId());
            List<ColumnLink> selections = qlc.getSelections(qsession.getSecurityGrade());
            for (ColumnLink c : selections) {
                int[] cids = c.getColumnIDs();
                if (c.getLastColumn().getReferenceTable() != null) {

                    //show ak, hide id
                    int[] rids = new int[cids.length + 1];

                    System.arraycopy(cids, 0, rids, 0, cids.length);
                    rids[rids.length - 1] = c.getLastColumn().getReferenceTable().getAlternateKey().getId();

                    query.addSelection(rids, true, c.getDescription(locale));
                } else {
                    query.addSelection(cids, false, c.getDescription(locale));
                }
            }
        } 
        
        if (qlc == null) {
            // not set
            log.error("Neither column_masks nor qlcid is found in query object:" + jo);
        }


        //add wgrade to set  readonly

//        JSONObject jopro = table.getJSONProps() == null ? new JSONObject() : table.getJSONProps();
//
//        if (jopro.has("wgrade")) {
//            JSONObject jor = jopro.getJSONObject("wgrade");
//            if (jor.has("colname")) {
//                query.addSelection((int) table.getColumn(jor.getString("colname")).getId());
//            }
//        }


        // Where
        parsewhere(query, jo, manager, qsession, qlc, table, locale);
        // Order
        parserange(query, jo, manager, qsession, qlc, table);

        return query;
    }


    private static void parsewhere(QueryRequestImpl query, JSONObject jo, TableManager manager, QuerySession qsession, QueryListConfig qlc, Table table, Locale locale) {
        Expression expr = null, expr2;
        String cs;

        //security param
        // directory perm
        int dirPerm = Tools.getInt(jo.getIntValue("dir_perm"), Directory.READ);
        expr2 = SecurityWebUtils.getSecurityFilter(table.getName(), dirPerm, qsession.getUser().getId(), qsession);
        //logger.debug("fixedColumns:"+ expr2.toString());
        if (expr2 != null && !expr2.isEmpty()) {
            expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
        }
        if (dirPerm == Directory.WRITE) {
            // try filter status column for only status=1 rows
            Column column = table.getColumn("status");
            if (column != null) {
                ColumnLink cl = new ColumnLink(new int[]{column.getId()}, manager);
                expr2 = new Expression(cl, "=1", Resources.getMessage("not-submit",locale));
                expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
            }
        }

        //support objectids expres
        PairTable pfixedColumns = null;
        try {
            pfixedColumns = PairTable.parseIntTable(jo.getString("objectIds"), null);
            for (Iterator it = pfixedColumns.keys(); it.hasNext(); ) {
                Integer key = (Integer) it.next();
                Column col = manager.getColumn(key.intValue());
                ColumnLink cl = new ColumnLink(col.getTable().getName() + "." + col.getName(), manager);
                expr2 = new Expression(cl, "=" + pfixedColumns.get(key), null);
                expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
            }
        } catch (NumberFormatException e) {
            pfixedColumns = PairTable.parse(jo.getString("objectIds"), null);
            expr2 = Expression.parsePairTable(pfixedColumns, manager);
            expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
        }


        /**
         *  system will try to load recent one week data if "param_str" is not set (this will cover
         *  the one week range) and "fixedColumns" not set (this occurs when item table is shown)
         *
         */
        //if( jo.optBoolean("tryrecent", false)){
        if (Validator.isNull(jo.getJSONObject("fixedcolumns"))) {
            //default to recent one week and status=1
            Column column;
            ArrayList columns = table.getIndexedColumns();
            boolean firstDateColumnFound = false;
            for (int i = 0; i < columns.size(); i++) {
                column = (Column) columns.get(i);
                if ("STATUS".equalsIgnoreCase(column.getName())) {
                    ColumnLink cl = new ColumnLink(new int[]{column.getId()}, manager);
                    expr2 = new Expression(cl, "=1", Resources.getMessage("not-submit", locale));
                    expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
                } else {
                    if (!firstDateColumnFound && (column.getType() == Column.DATE || column.getType() == Column.DATENUMBER)) {
                       // ColumnLink cl = new ColumnLink(new int[]{column.getId()}, manager);
                        if (column.getType() == Column.DATE) {
                            java.util.Calendar c = java.util.Calendar.getInstance();
                            c.setTimeInMillis(System.currentTimeMillis());
                            c.add(java.util.Calendar.DAY_OF_MONTH, -QueryUtils.DEFAULT_DATE_RANGE);
                            String startDate = "'" + QueryUtils.dateTimeSecondsFormatter.format(c) + "'";
                            String endDate = "'" + QueryUtils.dateTimeSecondsFormatter.format(new Date()) + "'";
                            expr2 = new Expression(null, table.getName() + "." + column.getName() + " between " + startDate + " and " +
                                    endDate,
                                    column.getDescription(locale) + Resources.getMessage("in-one-week", locale));

                        } else {
                            // date number
                            /**
                             * One week range can be updated by portal.properties#query.date.range
                             */
                            java.util.Calendar c = java.util.Calendar.getInstance();
                            c.setTimeInMillis(System.currentTimeMillis());
                            c.add(java.util.Calendar.DAY_OF_MONTH, -QueryUtils.DEFAULT_DATE_RANGE);
                            String startDate = "'" + QueryUtils.dateNumberFormatter.format(c) + "'";
                            String endDate = "'" + QueryUtils.dateNumberFormatter.format(new Date()) + "'";
                            expr2 = new Expression(null, table.getName() + "." + column.getName() + " between " + startDate + " and " +
                                    endDate, column.getDescription(locale) + Resources.getMessage("in-one-week", locale));
                        }
                        expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
                        firstDateColumnFound = true;
                    }
                }
            }
        } else {
            if(expr==null||expr.isEmpty()) {
                expr = QueryUtils.parseCondition(jo, manager, qsession, table);
            }else{
                expr=expr.combine(QueryUtils.parseCondition(jo, manager, qsession, table),SQLCombination.SQL_AND,null) ;
            }
        }//end fixedcolumns

        int[] ids;
        String data_search = jo.getString("quick_search_data");
        if (data_search != null && data_search.trim().length() > 0) {
            cs = jo.getString("quick_search_column");
            ids = QueryUtils.parseIntArray(cs);
            ColumnLink clnk2 = (new ColumnLink(ids, manager));
            String cond = checkCondition(clnk2.getLastColumn(), data_search, locale, manager);
            //logger.debug("query cond"+cond);
            if (cond != null) {
                expr2 = new Expression(clnk2, cond, null);
                expr = expr2.combine(expr, SQLCombination.SQL_AND, null);
            }
            log.debug("query cond" + cond);
        }


        /*由于在Column.Filter 上增加过滤器，此过滤器也将作用于界面，故此处将根据
         *      accepter_id 的结构解析出对应的Column 上的过滤器，主要的依据是 "column_" 后的id 内容
	     * 详见 nds.control.ejb.command.ObjectColumnObtain
        */
        Column returnColumn = manager.getColumn(Tools.getInt(jo.getIntValue("column"), -1));//
        if (returnColumn == null) {
            returnColumn = QueryUtils.getReturnColumn(jo.getString("accepter_id"), manager);
        }
        if (returnColumn != null) {
            //add column's filter to expr, 并且filter 不是wildcard filter, 否则web查询界面上会预先配置好，见search.jsp#q_form_param_expr
            if (returnColumn.getFilter() != null && !returnColumn.isFilteredByWildcard()) {
                Expression exprFilter = new Expression(null, returnColumn.getFilter(),
                        returnColumn.getDescription(locale) + Resources.getMessage("-have-special-filter", locale));
                if (expr != null) {
                    expr = expr.combine(exprFilter, SQLCombination.SQL_AND, null);
                } else {
                    expr = exprFilter;
                }
            }
        }

        if (expr != null) {
            //logger.debug("query expr"+expr.toString());
            query.addParam(expr);
        }
    }

    private static void parserange(QueryRequestImpl query, JSONObject jo, TableManager manager, QuerySession qsession, QueryListConfig qlc, Table table) {
        // range
        int startIdx = Tools.getInt(jo.getInteger("startindex"), 0);
        /*//support negative value now,meaning starting from last page
         * if( startIdx < 0)
            startIdx=0;*/
        int range = Tools.getInt(jo.getInteger("range"), QueryUtils.DEFAULT_RANGE);
        query.setRange(startIdx, range);
        // order
        int[] ids = QueryUtils.parseIntArray(jo.getString("order_columns"));
        if (ids != null && ids.length > 0) {
            boolean b = Tools.getBoolean(jo.getBoolean("order_asc"), true);
            query.setOrderBy(ids, b);
        } else {
            // so "order_columns" take previliage over "orderby"
            JSONArray orderby = jo.getJSONArray("orderby");
            if (orderby != null) {
                for (int i = 0; i < orderby.size(); i++) {
                    JSONObject od = orderby.getJSONObject(i);
                    try {
                        ColumnLink cl = new ColumnLink(od.getString("name"), manager);
                        query.addOrderBy(cl.getColumnIDs(), Tools.getBoolean(od.getBoolean("asc"), true));
                    } catch (Throwable t) {
                        log.error("fail to parse column link:" + table.getName() + "." + od.getString("column"), t);
                        throw new NDSException("order by column error:" + od.getString("column"));
                    }

                }
            } else {
                //orders from ui
//                orderby= jo.optJSONArray("orders");//elements can be converted to ColumnLink
//                if(orderby!=null){
//                    for(int i=0;i<orderby.length();i++){
//                        JSONObject od= orderby.getJSONObject(i);
//                        try{
//                            ColumnLink cl= ColumnLink.parseJSONObject(od);
//                            query.addOrderBy( cl.getColumnIDs(), !Boolean.FALSE.equals(cl.getTag()));
//                        }catch(Throwable t){
//                            logger.error("fail to parse column link:"+od , t);
//                            throw new NDSException("fail to parse column link:"+od);
//                        }
//
//                    }
//                }else{
                //load for qlc
                if (qlc != null) {
                    for (ColumnLink c : qlc.getOrderBys(qsession.getSecurityGrade())) {
                        query.addOrderBy(c.getColumnIDs(), !Boolean.FALSE.equals(c.getTag()));
                    }
                }
            }
        }


        boolean subtotal = Tools.getBoolean(jo.getBoolean("subtotal"), table.isSubTotalEnabled());
        query.enableFullRangeSubTotal(subtotal);
    }


    /**
     * 发现关于LimitValue 的字段在界面上直接输入描述选项时查询会出现错误。
     * 例如：状态字段 输入"提交" 时应该由系统自动转换为2
     * 如果发现Column.isValueLimited=true, 将设法替换其中的内容
     * 当前不处理增加了比较符的输入，即如果rawCondtion 含有除了 描述以外的符号，如"=", ">"之类
     * 将无法转换
     *
     * @param rawCondition 形如 "未提交"，"2"等
     * @return 重构的condition
     */
    private static String checkCondition(Column col, String rawCondition, Locale locale, TableManager tm) {
        if (rawCondition == null) {
            return rawCondition;
        }
        if (col.isValueLimited()) {
            String real = tm.getColumnValueByDescription(col.getId(), rawCondition.trim(), locale);
            if (real != null) {
                //logger.debug("Found " + col + ":" + rawCondition + " converted to real:"+ real+ "," + StringUtils.replace(rawCondition, rawCondition.trim(), real));
                return StringUtils.replace(rawCondition, rawCondition.trim(), real);
            }
        }
        return rawCondition;
    }


    /**
     * @param jo {
     *           combine: “and” | “or” | “and not” | “or not”
     *           expr1: expression,
     *           expr2 : expression
     *           }，或者 {
     *           expression
     *           }，expression 描述见下：
     *           expression :{
     *           column : column 名称或者ColumnLink，必须起始于table，忽略主表名称。
     *           condition : 字符串设置对应字段的条件, 例如 20090901~20091021表示日期的范围，>10表示数字字段的范围，输入方式与PORTAL界面一致
     *           } 通过expression设定某个字段满足某个条件，对于诸如exists类型的请求，可以设置column为空，在condition里直接输入exists(select x from y where z)类似的语句，注意主表在整体SQL语句构造时将被赋予全名称。
     * @return never null
     * @throws Exception
     */
    private static Expression parseExpression(JSONObject jo, Table mainTable, TableManager tm) throws Exception {

        if (jo == null) {
            return Expression.EMPTY_EXPRESSION;
        }
        Expression expr = null;
        String combine = jo.getString("combine");
        if (Validator.isNull(combine)) {
            //load column and description
            String condition = jo.getString("condition");
            String column = jo.getString("column");
            ColumnLink cl = null;
            if (Validator.isNotNull(column)) {
                cl = new ColumnLink(mainTable.getName() + "." + column, tm);
            }
            expr = new Expression(cl, condition, null);
        } else {
            Expression expr1 = parseExpression(jo.getJSONObject("expr1"), mainTable, tm);
            Expression expr2 = parseExpression(jo.getJSONObject("expr2"), mainTable, tm);
            expr = expr1.combine(expr2, Expression.parseCombination(combine), null);
        }
        return expr;
    }
}
